package com.merck.service.export;

import com.amazonaws.HttpMethod;
import com.amazonaws.services.s3.AmazonS3;
import com.amazonaws.services.s3.AmazonS3ClientBuilder;
import com.amazonaws.services.s3.model.*;
import com.amazonaws.services.s3.model.CannedAccessControlList;
import com.amazonaws.services.s3.model.GeneratePresignedUrlRequest;
import com.amazonaws.services.s3.model.PutObjectRequest;
import com.merck.service.impl.ImportDataServiceImpl;
import com.merck.service.read.ReadExcelImpl;
import com.merck.utils.DateUtils;
import com.merck.utils.SendMail;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;


//@Service("GenerateExcelImpl")
public class GenerateExcelQuarterImpl {



    private Logger log = Logger.getLogger(GenerateExcelQuarterImpl.class);

    public void exportExcel() {

        Properties pro = new Properties();
        Properties pros = new Properties();
        InputStream resourceAsStream = null;
        InputStream configStream = null;
        AmazonS3 s3Client = null;
        String configBucketName = null;
        String configRemoteFileName = null;
        try {
            s3Client = openS3ClientByEC2();
            configStream = GenerateExcelImpl.class.getClassLoader().getResourceAsStream("db.properties");
            pros.load(configStream);
            configBucketName = (String) pros.get("config.bucket");
            configRemoteFileName = (String) pros.get("config.remote");
            System.out.println("读取配置文件开始-----------");
//            resourceAsStream = ReadExcelImpl.class.getResourceAsStream("/config.properties");
//            pro.load(resourceAsStream);
            GetObjectRequest getObjectRequest = new GetObjectRequest(configBucketName, configRemoteFileName);
            S3Object s3Object = s3Client.getObject(getObjectRequest);
            resourceAsStream = s3Object.getObjectContent();
            pro.load(resourceAsStream);
            System.out.println("读取配置文件结束-----------");

            String bucketName;
            File file;
            ImportDataServiceImpl importData = new ImportDataServiceImpl();
            //SHEET 页对应的数据sql
            Map<String, String> map = null;
            Map<String, Map<String, String>> mapTitle = null;
            //到出的Excel名称
            String excelName = null;
            //当年 2021
            //从表中获取年份和季度
            String sql = "select * from lego_kpi_calculation.ads_kpi_final_report_qf where year_id is not null and quarter_id is not null limit 1";
            List<Map<String, Object>> list = importData.queryForListMap(sql);
            if (CollectionUtils.isEmpty(list)) {
                throw new RuntimeException("数据的年和季度为空！");
            }
            String quarterId = (String)list.get(0).get("quarter_id");
            String currentYear = quarterId.substring(0, 4);
            String currentQuarterly = quarterId.substring(4);

//            String currentYear = DateUtils.getCurrentYear();
//            String currentQuarterly = "Q" + DateUtils.getCurrentQuarterly();
            String dateStr = DateUtils.format(new Date(), "yyyyMMddHHmmss");
            List<File> attachments = new ArrayList<>();
            boolean delete;
            //导出路径
            String remoteFileName = null;
            String backupPath = (String) pro.get("backup.report.export.file.name") + "backup" + dateStr + "/" + currentYear + " " + currentQuarterly;
            String exportPath = (String) pro.get("report.export.file.name") + currentYear + " " + currentQuarterly;
            String name = currentYear + " " + currentQuarterly;


            /**
             * 导出Qutly KPI report FBU
             */
            mapTitle = TitleMap.getTitleMap("QFBU");
            map = new LinkedHashMap<>();
            map.put("KPI_FBU_REP_FD_C FD_S FD_P", SqlStr.KPI_FBU_REP_FD_C_FD_S_FD_P);
            map.put("KPI_FBU_DSM_FD_BA", SqlStr.KPI_FBU_DSM_FD_BA);
            map.put("KPI_FBU_DSM_FD_S FD_C FD_P", SqlStr.KPI_FBU_DSM_FD_S_FD_C_FD_P);
            map.put("KPI_FBU_FT DM_FT", SqlStr.KPI_FBU_FT_DM_FT);
            map.put("KPI_FBU_RSD&NAD", SqlStr.KPI_FBU_RSD_NAD);


            /**将excel生成进S3*/
          File file1 = new File(name + " Quarterly KPI Report FBU.xlsx");
          attachments.add(file1);
            log.info("开始生成excel表格");
            importData.exportDataExcleApp(map, file1, "excle", mapTitle, null);
            //导出
            bucketName = (String) pro.get("report.export.bucket.name");
            remoteFileName = exportPath + " Quarterly KPI Report FBU.xlsx";
            GenerateExcelIntoS3(remoteFileName, bucketName, file1);
//        备份
            bucketName = (String) pro.get("backup.report.export.bucket.name");
            remoteFileName = backupPath + " Quarterly KPI Report FBU.xlsx";
            GenerateExcelIntoS3(remoteFileName, bucketName, file1);
          log.info("导出季度FBU报表成功");

            //S3环境运行代码结束

            /**
             * 导出Quarterly KPI Report GBU
             */
            mapTitle = TitleMap.getTitleMap("QGBU");
            map = new LinkedHashMap<>();
            map.put("KPI_GBU_REP_CHD", SqlStr.KPI_GBU_REP_CHD);
            map.put("KPI_GBU_REP_DB_CV_MIX", SqlStr.KPI_GBU_REP_DB_CV_MIX);
            map.put("KPI_GBU_REP_CHD_DB", SqlStr.KPI_GBU_REP_CHD_DB);
            map.put("KPI_GBU_REP_TD_ST", SqlStr.KPI_GBU_REP_TD_ST);
            map.put("KPI_GBU_DSM_HA", SqlStr.KPI_GBU_DSM_HA);
            map.put("KPI_GBU_DSM_ST_TD", SqlStr.KPI_GBU_DSM_ST_TD);
            map.put("KPI_GBU_DSM_DB_CV_MIX_CHD", SqlStr.KPI_GBU_DSM_DB_CV_MIX_CHD);
            map.put("KPI_GBU_RSM", SqlStr.KPI_GBU_RSM);

          File file2 = new File(name + " Quarterly KPI Report GBU.xlsx");
          attachments.add(file2);
            //导出
            bucketName = (String) pro.get("report.export.bucket.name");
            remoteFileName = exportPath + " Quarterly KPI Report GBU.xlsx";
            importData.exportDataExcleApp(map, file2, "excle", mapTitle, null);
            GenerateExcelIntoS3(remoteFileName, bucketName, file2);
            //备份
            bucketName = (String) pro.get("backup.report.export.bucket.name");
            remoteFileName = backupPath + " Quarterly KPI Report GBU.xlsx";
            GenerateExcelIntoS3(remoteFileName, bucketName, file2);
          log.info("导出季度GBU报表成功");
            /**
             * 导出季度报表 Quarterly KPI Report KA
             */
            mapTitle = TitleMap.getTitleMap("QKA");
            map = new LinkedHashMap<>();
            map.put("KPI_KA_DSM_KA", SqlStr.KPI_KA_DSM_KA);
            //导出
         File file3 = new File(name + " Quarterly KPI Report KA.xlsx");
          attachments.add(file3);
            bucketName = (String) pro.get("report.export.bucket.name");
            remoteFileName = exportPath + " Quarterly KPI Report KA.xlsx";
            importData.exportDataExcleApp(map, file3, "excle", mapTitle, null);
            GenerateExcelIntoS3(remoteFileName, bucketName, file3);
            //备份
            bucketName = (String) pro.get("backup.report.export.bucket.name");
            remoteFileName = backupPath + " Quarterly KPI Report KA.xlsx";
            GenerateExcelIntoS3(remoteFileName, bucketName, file3);
          log.info("导出季度KA报表成功");

            /**
             * 导出季度报表 Quarterly KPI Report OBU
             */
            mapTitle = TitleMap.getTitleMap("QOBU");
            map = new LinkedHashMap<>();
            map.put("KPI_OBU_REP_Erbitux", SqlStr.KPI_OBU_REP_Erbitux);
            map.put("KPI_OBU_REP_Xalkori", SqlStr.KPI_OBU_REP_Xalkori);
            map.put("KPI_OBU_DSM", SqlStr.KPI_OBU_DSM);
            map.put("KPI_OBU_RSM", SqlStr.KPI_OBU_RSM);
         File file4 = new File(name + " Quarterly KPI Report OBU.xlsx");
          attachments.add(file4);
            //导出
            remoteFileName = exportPath + " Quarterly KPI Report OBU.xlsx";
            importData.exportDataExcleApp(map, file4, "excle", mapTitle, null);
            bucketName = (String) pro.get("report.export.bucket.name");
            GenerateExcelIntoS3(remoteFileName, bucketName, file4);
            //备份
            bucketName = (String) pro.get("backup.report.export.bucket.name");
            remoteFileName = backupPath + " Quarterly KPI Report OBU.xlsx";
          GenerateExcelIntoS3(remoteFileName, bucketName, file4);
          log.info("导出季度OBU报表成功");
          String currentDateAsString = DateUtils.getCurrentDateAsString();
          SendMail.sendMail(null, null, "导出季度报表数据任务在 " + currentDateAsString + " 运行结果通知", SqlStr.SUCESS_SEND_CONTENT, attachments);
          file1.delete();
          file2.delete();
          file3.delete();
          file4.delete();


            /**
             * 导出月份报表
             */
            //导出 Monthly KPI Report FBU
//        mapTitle = TitleMap.getMonthTitleMap("MFBU");
//        map = new LinkedHashMap<>();
//        map.put("KPI_FBU_REP_FD_C FD_S FD_P",SqlStr.KPI_FBU_REP_FD_C_FD_S_FD_P_M);
//        map.put("KPI_FBU_DSM_FD_BA",SqlStr.KPI_FBU_DSM_FD_BA_M);
//        map.put("KPI_FBU_DSM_FD_S FD_C FD_P",SqlStr.KPI_FBU_DSM_FD_S_FD_C_FD_P_M);
//        map.put("KPI_FBU_RSD&NAD",SqlStr.KPI_FBU_RSD_NAD_M);
//        //导出
//        file = new File("temp.xlsx");
//        bucketName = (String) pro.get("report.export.bucket.name");
//        remoteFileName =exportPath + " Monthly KPI Report FBU.xlsx";
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        //备份
//        bucketName = (String) pro.get("backup.report.export.bucket.name");
//        remoteFileName = backupPath + " Monthly KPI Report FBU.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        delete = file.delete();
//
//        //导出 Monthly KPI Report GBU
//        mapTitle = TitleMap.getMonthTitleMap("MGBU");
//        map = new LinkedHashMap<>();
//        map.put("KPI_GBU_DSM_ST_TD_DB_CV_MIX_CHD",SqlStr.KPI_GBU_DSM_ST_TD_DB_CV_MIX_CHD);
//        //导出
//        file = new File("temp.xlsx");
//        bucketName = (String) pro.get("report.export.bucket.name");
//        remoteFileName =exportPath + " Monthly KPI Report GBU.xlsx";
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        //备份
//        bucketName = (String) pro.get("backup.report.export.bucket.name");
//        remoteFileName = backupPath + " Monthly KPI Report GBU.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        delete = file.delete();
//
//        //导出 Monthly KPI Report OBU
//        mapTitle = TitleMap.getMonthTitleMap("MOBU");
//        map = new LinkedHashMap<>();
//        map.put("KPI_OBU_REP_Erbitux",SqlStr.KPI_OBU_REP_Erbitux_M);
//        map.put("KPI_OBU_REP_Xalkori",SqlStr.KPI_OBU_REP_Xalkori_M);
//        map.put("KPI_OBU_DSM",SqlStr.KPI_OBU_DSM_M);
//        //导出
//        file = new File("temp.xlsx");
//        bucketName = (String) pro.get("report.export.bucket.name");
//        remoteFileName =exportPath + " Monthly KPI Report OBU.xlsx";
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        //备份
//        bucketName = (String) pro.get("backup.report.export.bucket.name");
//        remoteFileName = backupPath + " Monthly KPI Report OBU.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        delete = file.delete();
//
        } catch (Exception e) {
            log.error(e.getMessage());
            String currentDateAsString = DateUtils.getCurrentDateAsString();
            SendMail.sendMail(null, null, "导出季度报表数据任务在 " + currentDateAsString + " 运行结果通知", SqlStr.FAILURE_SEND_CONTENT, null);
            log.error(e.getMessage());
        } finally {
            if (resourceAsStream != null) {
                try {
                    resourceAsStream.close();
                } catch (IOException e) {
                    log.error(e.getMessage());
                }
            }
        }

    }

    private void GenerateExcelIntoS3(String remoteFileName, String bucketName, File file) {
//        File file = new File("temp.xlsx");
//        log.info("开始生成excel表格");
//        String bucketName = (String) pro.get("export.bucket.name");
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);

        //S3环境运行代码开始
//        //把生成的Excel上传到S3，上传的路径为上面两个参数定义 ，为 s3://qa-lego-deployment/templates/ + fileName
//        //可以在HUE中查看
        AmazonS3 s3Client = null;
        try {
            s3Client = openS3ClientByEC2();

            PutObjectRequest poRequest = new PutObjectRequest(bucketName, remoteFileName, file).withCannedAcl(CannedAccessControlList.Private);
            s3Client.putObject(poRequest);
            GeneratePresignedUrlRequest urlRequest = new GeneratePresignedUrlRequest(bucketName, remoteFileName, HttpMethod.POST);
            s3Client.generatePresignedUrl(urlRequest);
//            file.delete();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (s3Client != null) {
                s3Client.shutdown();
            }
        }
    }

    public static AmazonS3 openS3ClientByEC2() {
        AmazonS3 s3Client = null;
        try {
            s3Client = AmazonS3ClientBuilder.defaultClient();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return s3Client;
    }


}
